<?php
namespace control;
/*
*多栏账类
*/

class multiColumnAccountClass extends adIndexClass
{
    private const page = 'multiColumnAccount';
    private const title = '多栏账';

    /**
     * 科目选择器
     */
    public static function subjectSelect($subjectId = '', $isShowParent = false): string
    {
        $subject = query('subject', "id = '$subjectId'");
        $selectorId = suiji();
        $html = "<span style='position: relative'>" . text(array('name' => 'subjectName', 'class' => 'subjectSelector', 'id' => $selectorId, 'title' => '科目', 'value' => $subject['code'] . $subject['name'])) . hidden(array('name' => 'subjectId', 'value' => $subject['id'])) . "<img onclick='chooseSubject(this,\"{$isShowParent}\")' style='position:absolute;right:10px;top:1px;cursor: pointer;' src='" . root . "img/control/find.svg?v=2'/></span>";
        $html .= <<<eof
<script>
$("#{$selectorId}").bigAutocomplete({
    url:root + 'control/adIndex.html?cla=component&fun=getSubject',
     param:{showParent:'$isShowParent'},
    callback:function(data,e){
       e.siblings("input[name='subjectId']").val(data.id)
       changeSubject(data.id)
}})

function chooseSubject(e,isShow){
    layer('component','subject',{isShow:isShow})
}
function chooseSubjectId(e){
    let id = $(e).data('id');
    let name = $(e).data('name');
    $(e).parent().find("li").css('background-color','white');
    $(e).css('background-color',"rgb(242, 242, 242)");
    $("[name='chooseId']").val(id);
    $("[name='chooseName']").val(name);
}
function confirmBtn(){
    let id =  $("[name='chooseId']").val();
    let name =  $("[name='chooseName']").val();
    if (!id){
        msg('请选择科目','error')
        return false;
    }
    $("[name='subjectId']").val(id);
    $("[name='subjectName']").val(name);
    $('#componentSubject').hide();
    changeSubject(id)
}
</script>
<style>
.subjectSelector{padding-right: 34px !important;width: 170px !important;}
.pageMenu{margin: 10px 0 0;}
.pageMenu > ul {
    padding: 0 20px;
}
.pageMenu > ul > li {
    float: left;
    padding: 6px 10px;
    background-color: rgb(255, 255, 255);
    cursor: pointer;
}
.subject-ul>li{padding: 2px 10px;cursor: pointer}
.subject-ul>li:hover{background-color: rgb(242, 242, 242)}
.subject-div{
    border: 1px solid rgba(215, 215, 215, 1);
    height: 450px;
    margin: 5px 0 5px 0;
    border-radius: 2px;
    overflow: auto;
    }
.confirm{
  width: 100px;
}
    .cancel,.cancel:hover{
        width: 100px;
        background-color: white;
        color: #7F7F7F;
        border: 1px solid rgba(215, 215, 215, 1);
        margin-right: 5px;
    }
    .bold{font-weight: bold}
</style>
eof;
        return $html;
    }

    /**
     * 获取科目选项
     * @return mixed
     */
    private function getSearchSubject()
    {
        $default = static::rGet(self::$stid . self::page . 'SubjectId');
        $all = find(array('table' => "subject", 'where' => "id in (select distinct subjectId from voucher_detail where coid = '" . self::$coid . "') order by code asc"));
        foreach ($all['array'] as $k => $array) {
            if ($k == 0 && empty($default)) {
                $default = $array['id'];
            }
            $option[$array['id']] = $array['code'] . ' ' . $array['name'];
        }
        static::rSet(self::$stid . self::page . 'SubjectId', $default);
        return $default;
    }

    /**
     * 切换按钮
     * @return string
     */
    private function searchButton()
    {
        $subjectId = $this->getSearchSubject();
        $subject = query('subject', "id = '$subjectId'");
        if ($subject['assistAccount'] == '是') {
            $assistAccountIds = explode(',', $subject['assistAccountIds']);
            $defaultAssit = static::rGet(self::$stid . self::page . 'Assit');//辅助核算选项
            if (empty($defaultAssit)) {
                $defaultAssit = $assistAccountIds[0];
                static::rSet(self::$stid . self::page . 'Assit', $defaultAssit);
            }
            //辅助核算科目
            $button = self::subjectSelect($subjectId, true) . "
                       <select class='select' style='margin-left: 8px' onchange='changeAssistAccountIds(this)'>" . option(array('value' => $defaultAssit, 'option' => $assistAccountIds)) . "</select>";
        } elseif (static::total(array('table' => 'subject', 'where' => "pid = '$subjectId'")) > 0) {
            //不是末级科目
            $defaultAssit = static::rGet(self::$stid . self::page . 'Assit');//辅助核算选项
            if (empty($defaultAssit)) {
                $defaultAssit = '下级科目';
                static::rSet(self::$stid . self::page . 'Assit', $defaultAssit);
            }
            $button = self::subjectSelect($subjectId, true) . "
                       <select class='select' style='margin-left: 8px' onchange='changeAssistAccountIds(this)'>" . option(array('value' => $defaultAssit, 'option' => ['下级科目', '末级科目'])) . "</select>";
        } else {
            //末级科目且非辅助核算科目
            //不是末级科目
            $defaultAssit = static::rGet(self::$stid . self::page . 'Assit');//辅助核算选项
            if (empty($defaultAssit)) {
                $defaultAssit = '下级科目';
                static::rSet(self::$stid . self::page . 'Assit', $defaultAssit);
            }
            $button = self::subjectSelect($subjectId, true) . "
                       <select class='select' style='margin-left: 8px' onchange='changeAssistAccountIds(this)'>" . option(array('value' => $defaultAssit, 'option' => ['下级科目', '末级科目'])) . "</select>";
        }
        return "<span class='search' style='display: inline-block;padding: 0;margin-left: 8px'>" . $button . "</span><style>.search .fs-wrap{vertical-align: text-bottom} .search .fs-label {height: 17px;line-height: 17px;}</style>";
    }

    /**
     * @return string
     */
    public function style(): string
    {
        return <<<eof
<style>
.tableMany thead tr:nth-of-type(2) {  
    background-color: #f2f2f2;
    color: #555;  
}
</style>
eof;
    }

    private function js()
    {
        return <<<eof
<script>
//切换科目
function changeSubject(subjectId){
       $.post(root+'control/adIndex.html?cla=multiColumnAccount&fun=changeSubject',{subjectId:subjectId},function(res) {
            if (res.warn==2){
                location.reload();
            }else{
                msg(res.warn,'error')
            }
       },'json')
}

function changeAssistAccountIds(e){
    let val = e.value;
   $.post(root+'control/adIndex.html?cla=multiColumnAccount&fun=changeAssistAccountIds',{assitVal:val},function(res) {
        if (res.warn==2){
            location.reload();
        }else{
            msg(res.warn,'error')
        }
   },'json')
}
$("#subjectTable tbody tr").each(function() {
    $(this).click(function() {
      tableManyClick($(this))
    })
})
// $(function() {
//   setTimeout(function() {
//     $('#clone_subjectTable_head').fixedThead('destroy');
//   },100)
// })
</script>
<style>
#homeSearchHtml{overflow: auto}
</style>
eof;

    }

    /**
     * 科目切换
     * @return bool|string
     */
    public function changeSubject(): bool|string
    {
        $subjectId = $this->post('subjectId');
        if (empty($subjectId)) {
            $json['warn'] = '请选择科目';
        } else {
            $json['warn'] = 2;
            static::rSet(self::$stid . self::page . 'SubjectId', $subjectId);
            static::rSet(self::$stid . self::page . 'Assit', '');
            $this->searchButton();
        }
        return json_encode($json, 320);
    }

    /**
     * 辅助选项切换
     * @return false|string
     */
    public function changeAssistAccountIds(): bool|string
    {
        $assitVal = $this->post('assitVal');
        if (empty($assitVal)) {
            $json['warn'] = '未找到选项';
        } else {
            $json['warn'] = 2;
            static::rSet(self::$stid . self::page . 'Assit', $assitVal);
        }
        return json_encode($json, 320);
    }

    /*列表页-查询条件*/
    public function homeOptions($isAsync = true)
    {
        $this->getSearchSubject();
        $maxDate = accountDetailsClass::getMaxDate();
        $minDate = accountDetailsClass::getMinDate();
        //生成查询表单
        $html = $this->tableShow([
            [
                [
                    "会计期间" => text(array("name" => "createTime", 'value' => static::rGet(self::$stid .self::$coid. 'SearchMonth') ?: date('Y-m',strtotime($maxDate)) . ' ~ ' . date('Y-m',strtotime($maxDate)),  'laydate' => ['type' => 'month','range'=>'~','min'=>"'{$minDate}'",'max'=>"'{$maxDate}'"])),
                ]
            ]
        ],1);
        //返回
        if (!$isAsync) {
            return $html;
        } else {
            return json_encode(['html' => $html], 320);
        }
    }

    /*列表-查询条件*/
    public function home()
    {
        $html = $this->homeOptions(false);
        //控件
        $button = "";
        if ($this->power(self::page, "export")) {
            $button .= "  <a href='" . root . "control/adIndex.html?cla=" . $this->get('cla') . "&fun=exportData'><span class='spanButton'>导出</span></a>\n";
        }
        $button .= $this->searchButton();
        //返回
        return $this->homeFun($this->searchExecute([
            'html' => $html,
            'layout' => 'pop',
            'toolButton' => $button,
            'searchOption' => '{}',
            'option' => '{fixed:true, row:2}'
        ]));
    }


    /*客户列表-查询结果*/
    public function homeSearch()
    {
        static::rSet(self::$stid .self::$coid. 'SearchMonth', $this->post('createTime'));;
        $subjectId = static::rGet(self::$stid . self::page . 'SubjectId');
        //判断当前科目是否是辅助核算或者是否是末级科目
        $subject = query('subject', "id = '" . $subjectId . "'");
        if ($subject['assistAccount'] == '是') {
            $json['html'] = $this->assetStyle();//辅助核算
        } else {
            $json['html'] = $this->subjectDetailStyle();//明细科目
        }
        $json['html'] .= $this->style();
        $json['html'] .= $this->js();
        return json_encode($json, 320);
    }


    /**
     * 辅助核算样式
     * @return string
     */
    public function assetStyle(): string
    {
        //辅助选项
        $assitItem = static::rGet(self::$stid . self::page . 'Assit');
        $subjectId = static::rGet(self::$stid . self::page . 'SubjectId');
        $subject = query('subject', "id = '$subjectId'");
        //like查询数组
        $like = array();
        //全等查询数组
        $equal = array(
            "b.subjectId" => $subject['id'],
            'a.isTemplate' => '否',
            'a.coid' => self::$coid
        );
        $and = match ($assitItem) {
            '客户' => " and khid !=''",
            '供应商' => " and supplierId !=''",
            '员工' => " and staffId !=''",
            '部门' => " and departmentId !=''",
            '存货' => " and materialId !=''",
            '现金流' => " and cashId !=''",
            '项目' => " and projectId !=''",
            default => ''
        };
        $arr        = $this->post('createTime') ? explode(' ~ ', $this->post('createTime')) : [date('Y-m', strtotime(accountDetailsClass::getMinDate())), date('Y-m', strtotime(accountDetailsClass::getMaxDate()))];
        $arr[0] = trim($arr[0]);
        $arr[1] = trim(end($arr));
        $startMonth = trim($arr[0]);
        $and .= " and date_format(a.recordDate,'%Y-%m') >= '$arr[0]' and date_format(a.recordDate,'%Y-%m') <='$arr[1]'";
        //获取查询结果
        $where = search(array("like" => $like, "=" => $equal)) . $and . "";
        $page = $this->page(array("table" => "voucher as a join voucher_detail as b on a.id = b.orderId", 'field' => "b.*,a.voucherNo,a.recordDate,a.voucherWordId", "where" => $where . voucherClass::joinWhere(), "order" => "recordDate asc,a.voucherNo asc,b.list"));

        static::rSet(self::$stid . self::page . 'sql', $page['fullSql']);
        static::rSet(self::$stid . self::page . 'time', $this->post('createTime'));
        static::rSet(self::$stid . self::page . 'where', $where);

        $tr = '';
        $url = root . "control/adIndex.html?cla=voucherEdit&fun=home";
        $accountDetail = new accountDetailsClass();
        //获取表头
        foreach ($page['array'] as $index => $array) {
            $array['money'] = self::centToYuan($array['money']);
            $voucherWord = query('voucher_word', "id = '$array[voucherWordId]'");
            if ($index == 0) {
                //期初
                $initResult = $accountDetail->initMoney($subject['id'], $startMonth);
                $jieTd = '';
                $columns = $this->getColumns($where, '借');
                foreach ($columns as $val) {
                    $jieTd .= "<td></td>";
                }
                $daiTd = '';
                $columns = $this->getColumns($where, '贷');
                foreach ($columns as $val) {
                    $daiTd .= "<td></td>";
                }
                $tr .= <<<eof
                 <tr>
                     <td>{$array['recordDate']}</td>
                     <td></td>
                     <td>期初余额</td>
                     <td>{$initResult['jieMoney']}</td>
                     <td>{$initResult['daiMoney']}</td>
                     <td>{$initResult['direction']}</td>
                     <td>{$initResult['balance']}</td>
                     $jieTd
                     $daiTd
                 </tr>
eof;
            }
            $pinzheng = $voucherWord['id'] ? componentClass::urlTitle($voucherWord['word'] . '-' . $array['voucherNo'], $url . "&id={$array['orderId']}", '录凭证') : '';

            //借
            $columns = $this->getColumns($where, '借');
            $array['money'] = number_format($array['money'], 2);
            $jieTd = $this->trContent($columns, $assitItem, $array, '借');
            //贷
            $columns = $this->getColumns($where, '贷');
            $daiTd = $this->trContent($columns, $assitItem, $array, '贷');
            $jieMoney = $daiMoney = '';
            $balance = $accountDetail->balance($array['recordDate'], $subject['id'], $array['voucherNo']);
            if ($array['direction'] == '借') {
                $jieMoney = $array['money'];
            } else {
                $daiMoney = $array['money'];
            }
            $tr .= <<<eof
                 <tr>
                     <td>{$array['recordDate']}</td>
                     <td>$pinzheng</td>
                     <td>{$array['summary']}</td>
                     <td>$jieMoney</td>
                     <td>$daiMoney</td>
                     <td>{$balance['direction']}</td>
                     <td>{$balance['balance']}</td>
                     $jieTd
                     $daiTd
                 </tr>
eof;
            if (!isset($page['array'][$index + 1]['recordDate']) || date('Y-m', strtotime($page['array'][$index + 1]['recordDate'])) != date('Y-m', strtotime($array['recordDate']))) {
                $periodMonth = date('Y-m', strtotime($array['recordDate']));
                $jieTd = '';
                $columns = $this->getColumns($where, '借');
                foreach ($columns as $val) {
                    $res = $this->assestSelfPeriod($assitItem, $periodMonth, $subjectId, $val);
                    $jieTd .= "<td>{$res['jieMoney']}</td>";
                }
                $daiTd = '';
                $columns = $this->getColumns($where, '贷');
                foreach ($columns as $val) {
                    $res = $this->assestSelfPeriod($assitItem, $periodMonth, $subjectId, $val);
                    $daiTd .= "<td>{$res['daiMoney']}</td>";
                }
                $selfPeriod = $this->assestSelfPeriod($assitItem, $periodMonth, $subjectId);
                $selfPeriodBalance = $this->assestBalance($assitItem, $periodMonth, $subjectId);
                $jieYearTd = $daiYearTd = '';
                $yearPeriod = $this->assestYearPeriod($assitItem, $periodMonth, $subjectId);
                $columns = $this->getColumns($where, '借');
                foreach ($columns as $val) {
                    $res = $this->assestYearPeriod($assitItem, $periodMonth, $subjectId, $val);
                    $jieYearTd .= "<td>{$res['jieMoney']}</td>";
                }
                $columns = $this->getColumns($where, '贷');
                foreach ($columns as $val) {
                    $res = $this->assestYearPeriod($assitItem, $periodMonth, $subjectId, $val);
                    $daiYearTd .= "<td>{$res['daiMoney']}</td>";
                }

                $jieBalanceTd = $daiBalanceTd = '';
                $columns = $this->getColumns($where, '借');
                foreach ($columns as $val) {
                    $jieBalanceTd .= "<td></td>";
                }
                $columns = $this->getColumns($where, '贷');
                foreach ($columns as $val) {
                    $daiBalanceTd .= "<td></td>";
                }
                $tr .= <<<eof
                 <tr class="bold">
                     <td>$periodMonth</td>
                     <td></td>
                     <td>本期合计</td>
                     <td>{$selfPeriod['jieMoney']}</td>
                     <td>{$selfPeriod['daiMoney']}</td>
                     <td>{$selfPeriodBalance['direction']}</td>
                     <td>{$selfPeriodBalance['balance']}</td>
                     $jieTd
                     $daiTd
                 </tr>
                 <tr class="bold">
                     <td>$periodMonth</td>
                     <td></td>
                     <td>本年累计</td>
                     <td>{$yearPeriod['jieMoney']}</td>
                     <td>{$yearPeriod['daiMoney']}</td>
                     <td>{$selfPeriodBalance['direction']}</td>
                     <td>{$selfPeriodBalance['balance']}</td>
                     $jieYearTd
                     $daiYearTd
                 </tr>
                 <tr class="bold">
                     <td>$periodMonth</td>
                     <td></td>
                     <td>期末余额</td>
                     <td></td>
                     <td></td>
                     <td>{$selfPeriodBalance['direction']}</td>
                     <td>{$selfPeriodBalance['balance']}</td>
                     $jieBalanceTd
                     $daiBalanceTd
                 </tr>
eof;
            }

        }

        //借-列
        $columns = $this->getColumns($where, '借');
        $jieColumnsNum = count($columns);
        $jieTd = $this->trTitle($columns, $assitItem);
        $jieHide = '';
        if (empty($jieTd)) {
            $jieHide = 'hide';
        }
        //贷-列
        $columns = $this->getColumns($where, '贷');
        $daiColumnsNum = count($columns);
        $daiTd = $this->trTitle($columns, $assitItem);
        $daiHide = '';
        if (empty($daiTd)) {
            $daiHide = 'hide';
        }
        // 月份选择器
        $monthSelector = componentClass::monthSelector(implode('~',$arr));
        $balanceBut = $jieBut = $datBut = '';
        if($jieHide && $daiHide){
            $balanceBut = $monthSelector['button'];
        }elseif($daiHide){
            $jieBut = $monthSelector['button'];
        }else{
            $datBut = $monthSelector['button'];
        }
        $html = <<<eof
<table id="subjectTable" class="tableMany" style="margin: unset;width: unset;min-width: 1500px">
    <thead>
        <tr>
            <td rowspan="2" style="vertical-align:middle">日期</td>
            <td rowspan="2" style="vertical-align:middle">凭证号</td>
            <td rowspan="2" style="vertical-align:middle">摘要</td>
            <td rowspan="2" style="vertical-align:middle">借方</td>
            <td rowspan="2" style="vertical-align:middle">贷方</td>
            <td rowspan="2" style="vertical-align:middle">方向</td>
            <td rowspan="2" style="vertical-align:middle">余额 {$balanceBut}</td>
            <td colspan="$jieColumnsNum" class="center $jieHide">借方 {$jieBut}</td>
            <td colspan="$daiColumnsNum" class="center $daiHide">贷方 {$datBut}</td>
        </tr>
        <tr>
            $jieTd
            $daiTd
        </tr>
    </thead>
    
    <tbody>
       $tr
    </tbody>
</table>
<style>
#subjectTable td{white-space:nowrap}
#subjectTable{width: unset;min-width: 1500px;margin-left: 0}
</style>

eof;
        //返回
        return $page['hint'] .
            "<div class='acct-main-container'>
                <form name='listForm'>" .
                    $html .
                "</form>" .
                $monthSelector['html'].
            "</div>" .
            $page['button'] .
            clear;
    }

    /**
     * 辅助核算-本年累计
     */
    private function assestYearPeriod($type, $month, $subjectId, $targetId = ''): array
    {
        if ($targetId) {
            $where = match ($type) {
                '客户' => " and khid = '$targetId'",
                '员工' => " and staffId = '$targetId'",
                '供应商' => " and supplierId = '$targetId'",
                '存货' => " and materialId = '$targetId'",
                '现金流' => " and cashId = '$targetId'",
                '部门' => " and departmentId = '$targetId'",
                '项目' => " and projectId = '$targetId'",
                default => ' and 1!=1'
            };
        } else {
            $where = match ($type) {
                '客户' => " and khid !=''",
                '供应商' => " and supplierId !=''",
                '员工' => " and staffId !=''",
                '部门' => " and departmentId !=''",
                '存货' => " and materialId !=''",
                '现金流' => " and cashId !=''",
                '项目' => " and projectId !=''",
                default => ' and 1!=1'
            };
        }
        $year = date('Y', strtotime($month . '-01'));
        $jieInit = find(array("table" => "voucher as a join voucher_detail as b on a.id = b.orderId", 'field' => "ifnull(sum(b.money),0) as money", "single" => "a.isTemplate = '否' and a.coid = '" . self::$coid . "' and b.subjectId = '$subjectId' and b.direction = '借' and date_format(a.recordDate,'%Y-%m') <= '$month' and date_format(a.recordDate,'%Y') = '$year'" . $where . voucherClass::joinWhere()));
        $daiInit = find(array("table" => "voucher as a join voucher_detail as b on a.id = b.orderId", 'field' => "ifnull(sum(b.money),0) as money", "single" => "a.isTemplate = '否' and a.coid = '" . self::$coid . "' and b.subjectId = '$subjectId' and b.direction = '贷' and date_format(a.recordDate,'%Y-%m') <= '$month' and date_format(a.recordDate,'%Y') = '$year'" . $where . voucherClass::joinWhere()));
        $jieInit['money'] = self::centToYuan($jieInit['money']);
        $daiInit['money'] = self::centToYuan($daiInit['money']);
        return ['jieMoney' => voucherClass::zeroToEmpty(number_format($jieInit['money'], 2)), 'daiMoney' => voucherClass::zeroToEmpty(number_format($daiInit['money'], 2))];
    }

    /**
     * 辅助核算-本期合计
     */
    private function assestSelfPeriod($type, $month, $subjectId, $targetId = '')
    {
        if ($targetId) {
            $where = match ($type) {
                '客户' => " and khid = '$targetId'",
                '员工' => " and staffId = '$targetId'",
                '供应商' => " and supplierId = '$targetId'",
                '存货' => " and materialId = '$targetId'",
                '现金流' => " and cashId = '$targetId'",
                '部门' => " and departmentId = '$targetId'",
                '项目' => " and projectId = '$targetId'",
                default => ' and 1!=1'
            };
        } else {
            $where = match ($type) {
                '客户' => " and khid !=''",
                '供应商' => " and supplierId !=''",
                '员工' => " and staffId !=''",
                '部门' => " and departmentId !=''",
                '存货' => " and materialId !=''",
                '现金流' => " and cashId !=''",
                '项目' => " and projectId !=''",
                default => ' and 1!=1'
            };
        }


        $jieInit = find(array("table" => "voucher as a join voucher_detail as b on a.id = b.orderId", 'field' => "ifnull(sum(b.money),0) as money", "single" => "a.isTemplate = '否' and a.coid = '" . self::$coid . "' and b.subjectId = '$subjectId'  and b.direction = '借' and date_format(a.recordDate,'%Y-%m') = '$month'" . $where . voucherClass::joinWhere()));
        $daiInit = find(array("table" => "voucher as a join voucher_detail as b on a.id = b.orderId", 'field' => "ifnull(sum(b.money),0) as money", "single" => "a.isTemplate = '否' and a.coid = '" . self::$coid . "' and b.subjectId = '$subjectId'  and b.direction = '贷' and date_format(a.recordDate,'%Y-%m') = '$month'" . $where . voucherClass::joinWhere()));
        $jieInit['money'] = self::centToYuan($jieInit['money']);
        $daiInit['money'] = self::centToYuan($daiInit['money']);
        return ['jieMoney' => voucherClass::zeroToEmpty(number_format($jieInit['money'], 2)), 'daiMoney' => voucherClass::zeroToEmpty(number_format($daiInit['money'], 2))];
    }

    /**
     * 现金流-计算余额
     * @param $type
     * @param $month
     * @param $subjectId
     * @return array
     */
    public function assestBalance($type, $month, $subjectId): array
    {
        $where = match ($type) {
            '客户' => " and khid !=''",
            '供应商' => " and supplierId !=''",
            '员工' => " and staffId !=''",
            '部门' => " and departmentId !=''",
            '存货' => " and materialId !=''",
            '现金流' => " and cashId !=''",
            '项目' => " and projectId !=''",
            default => ' and 1!=1'
        };
        //期初金额
        $jieInit = find(array('table' => "subject_assist as a join subject as b on a.orderId = b.id", 'single' => "b.direction = '借' and b.id = '$subjectId'" . $where, 'field' => "ifnull(sum(a.initMoney),0) as money,ifnull(sum(a.initNum),0) as num"));
        $daiInit = find(array('table' => "subject_assist as a join subject as b on a.orderId = b.id", 'single' => "b.direction = '贷' and b.id = '$subjectId'" . $where, 'field' => "ifnull(sum(a.initMoney),0) as money,ifnull(sum(a.initNum),0) as num"));

        $jievoucher = find(array("table" => "voucher as a join voucher_detail as b on a.id = b.orderId", 'field' => "ifnull(sum(b.money),0) as money", "single" => "a.isTemplate = '否' and a.coid = '" . self::$coid . "' and b.subjectId = '$subjectId' and b.direction = '借' and date_format(a.recordDate,'%Y-%m') <= '$month'" . $where . voucherClass::joinWhere()));
        $daivoucher = find(array("table" => "voucher as a join voucher_detail as b on a.id = b.orderId", 'field' => "ifnull(sum(b.money),0) as money", "single" => "a.isTemplate = '否' and a.coid = '" . self::$coid . "' and b.subjectId = '$subjectId' and b.direction = '贷' and  date_format(a.recordDate,'%Y-%m') <= '$month'" . $where . voucherClass::joinWhere()));
        $jieMoney = self::centToYuan($jievoucher['money']) + $jieInit['money'];
        $daiMoney = self::centToYuan($daivoucher['money']) + $daiInit['money'];
        if ($jieMoney == $daiMoney && $jieMoney == 0) {
            $direction = '平';
            $balance = '';
        } elseif ($jieMoney > $daiMoney) {
            $direction = '借';
            $balance = number_format(abs($jieMoney - $daiMoney), 2);
        } else {
            $direction = '贷';
            $balance = number_format(abs($jieMoney - $daiMoney), 2);
        }
        return [
            'direction' => $direction,
            'balance' => $balance
        ];
    }

    /**
     * tbody组合
     */
    private function trContent($columns, $assitItem, $array, $direction): string
    {
        $html = '';
        foreach ($columns as $val) {
            switch ($assitItem) {
                case '客户':
                    if ($val == $array['khid'] && $direction == $array['direction']) {
                        $html .= "<td>{$array['money']}</td>";
                    } else {
                        $html .= "<td></td>";
                    }
                    break;
                case '供应商':
                    if ($val == $array['supplierId'] && $direction == $array['direction']) {
                        $html .= "<td>{$array['money']}</td>";
                    } else {
                        $html .= "<td></td>";
                    }
                    break;
                case '员工':
                    if ($val == $array['staffId'] && $direction == $array['direction']) {
                        $html .= "<td>{$array['money']}</td>";
                    } else {
                        $html .= "<td></td>";
                    }
                    break;
                case '部门':
                    if ($val == $array['departmentId'] && $direction == $array['direction']) {
                        $html .= "<td>{$array['money']}</td>";
                    } else {
                        $html .= "<td></td>";
                    }
                    break;
                case '存货':
                    if ($val == $array['materialId'] && $direction == $array['direction']) {
                        $html .= "<td>{$array['money']}</td>";
                    } else {
                        $html .= "<td></td>";
                    }
                    break;
                case '现金流':
                    if ($val == $array['cashId'] && $direction == $array['direction']) {
                        $html .= "<td>{$array['money']}</td>";
                    } else {
                        $html .= "<td></td>";
                    }
                    break;
                case '项目':
                    if ($val == $array['projectId'] && $direction == $array['direction']) {
                        $html .= "<td>{$array['money']}</td>";
                    } else {
                        $html .= "<td></td>";
                    }
                    break;
            }
        }
        return $html;
    }

    /**
     * 表头组合
     */
    private function trTitle($columns, $assitItem): string
    {
        $html = '';
        foreach ($columns as $val) {
            switch ($assitItem) {
                case '客户':
                    $kehu = query('kehu', "khid = '$val'");
                    $html .= "<td>{$kehu['companyName']}</td>";
                    break;
                case '供应商':
                    $info = query('supplier', "id = '$val'");
                    $html .= "<td>{$info['name']}</td>";
                    break;
                case '员工':
                    $info = query('staff', "stid = '$val'");
                    $html .= "<td>{$info['name']}</td>";
                    break;
                case '部门':
                    $info = query('department', "id = '$val'");
                    $html .= "<td>{$info['name']}</td>";
                    break;
                case '存货':
                    $material = query('material', "id = '$val'");
                    $html .= "<td>{$material['code']}{$material['name']}{$material['model']}</td>";
                    break;
                case '项目':
                    $info = query('project', "id = '$val'");
                    $html .= "<td>{$info['name']}</td>";
                    break;
                case '现金流':
                    $info = query('cash', "id = '$val'");
                    $html .= "<td>{$info['name']}</td>";
                    break;
                default:
                    $html .= "<td></td>";
                    break;
            }
        }
        return $html;
    }

    /**
     * 导出内容
     */
    private function excelContent($columns, $assitItem, $array,$direction)
    {
        $html = [];
        foreach ($columns as $val) {
            switch ($assitItem) {
                case '客户':
                    if ($val == $array['khid'] && $direction == $array['direction']) {
                        $html[] = $array['money'];
                    } else {
                        $html[] = '';
                    }
                    break;
                case '供应商':
                    if ($val == $array['supplierId'] && $direction == $array['direction']) {
                        $html[] = $array['money'];
                    } else {
                        $html[] = '';
                    }
                    break;
                case '员工':
                    if ($val == $array['staffId'] && $direction == $array['direction']) {
                        $html[] = $array['money'];
                    } else {
                        $html[] = '';
                    }
                    break;
                case '部门':
                    if ($val == $array['departmentId'] && $direction == $array['direction']) {
                        $html[] = $array['money'];
                    } else {
                        $html[] = '';
                    }
                    break;
                case '存货':
                    if ($val == $array['materialId'] && $direction == $array['direction']) {
                        $html[] = $array['money'];
                    } else {
                        $html[] = '';
                    }
                    break;
                case '现金流':
                    if ($val == $array['cashId'] && $direction == $array['direction']) {
                        $html[] = $array['money'];
                    } else {
                        $html[] = '';
                    }
                    break;
                case '项目':
                    if ($val == $array['projectId'] && $direction == $array['direction']) {
                        $html[] = $array['money'];
                    } else {
                        $html[] = '';
                    }
                    break;
            }
        }
        return $html;
    }

    /**
     * 表头组合
     */
    private function excelTitle($columns, $assitItem, $direction)
    {
        $html = [];
        foreach ($columns as $val) {
            switch ($assitItem) {
                case '客户':
                    $kehu = query('kehu', "khid = '$val'");
                    $html[] = $direction . '-' . $kehu['companyName'];
                    break;
                case '供应商':
                    $info = query('supplier', "id = '$val'");
                    $html[] = $direction . '-' . $info['name'];
                    break;
                case '员工':
                    $info = query('staff', "stid = '$val'");
                    $html[] = $direction . '-' . $info['name'];
                    break;
                case '部门':
                    $info = query('department', "id = '$val'");
                    $html[] = $direction . '-' . $info['name'];
                    break;
                case '存货':
                    $material = query('material', "id = '$val'");
                    $html[] = $direction . '-' . $material['code'] . $material['name'] . $material['model'];
                    break;
                case '项目':
                    $info = query('project', "id = '$val'");
                    $html[] = $direction . '-' . $info['name'];
                    break;
                case '现金流':
                    $info = query('cash', "id = '$val'");
                    $html[] = $direction . '-' . $info['name'];
                    break;
                default:
                    $html[] = '';
                    break;
            }
        }
        return $html;
    }

    /**
     * 获取列数
     */
    private function getColumns($where, $type): array
    {
        $result = [];
        $assitItem = static::rGet(self::$stid . self::page . 'Assit');
        $where .= $type == '借' ? " and direction = '借' and a.coid = '" . self::$coid . "'" : " and direction = '贷' and a.coid = '" . self::$coid . "'";
        switch ($assitItem) {
            case '客户':
                $all = find(array("table" => "voucher as a join voucher_detail as b on a.id = b.orderId", 'field' => "distinct khid", "where" => $where . voucherClass::joinWhere() . " order by any_value(recordDate) asc,any_value(a.voucherNo) asc,any_value(b.list)"));
                foreach ($all['array'] as $item) {
                    $result[] = $item['khid'];
                }
                break;
            case '供应商':
                $all = find(array("table" => "voucher as a join voucher_detail as b on a.id = b.orderId", 'field' => "distinct supplierId", "where" => $where . voucherClass::joinWhere() . " order by any_value(recordDate) asc,any_value(a.voucherNo) asc,any_value(b.list)"));
                foreach ($all['array'] as $item) {
                    $result[] = $item['supplierId'];
                }
                break;
            case '员工':
                $all = find(array("table" => "voucher as a join voucher_detail as b on a.id = b.orderId", 'field' => "distinct staffId", "where" => $where . voucherClass::joinWhere() . " order by any_value(recordDate) asc,any_value(a.voucherNo) asc,any_value(b.list)"));
                foreach ($all['array'] as $item) {
                    $result[] = $item['staffId'];
                }
                break;
            case '部门':
                $all = find(array("table" => "voucher as a join voucher_detail as b on a.id = b.orderId", 'field' => "distinct departmentId", "where" => $where . voucherClass::joinWhere() . " order by any_value(recordDate) asc,any_value(a.voucherNo) asc,any_value(b.list)"));
                foreach ($all['array'] as $item) {
                    $result[] = $item['departmentId'];
                }
                break;
            case '存货':
                $all = find(array("table" => "voucher as a join voucher_detail as b on a.id = b.orderId", 'field' => "distinct materialId", "where" => $where . voucherClass::joinWhere() . " order by any_value(recordDate) asc,any_value(a.voucherNo) asc,any_value(b.list)"));
                foreach ($all['array'] as $item) {
                    $result[] = $item['materialId'];
                }
                break;
            case '现金流':
                $all = find(array("table" => "voucher as a join voucher_detail as b on a.id = b.orderId", 'field' => "distinct cashId", "where" => $where . voucherClass::joinWhere() . " order by any_value(recordDate) asc,any_value(a.voucherNo) asc,any_value(b.list)"));
                foreach ($all['array'] as $item) {
                    $result[] = $item['cashId'];
                }
                break;
            case '项目':
                $all = find(array("table" => "voucher as a join voucher_detail as b on a.id = b.orderId", 'field' => "distinct projectId", "where" => $where . voucherClass::joinWhere() . " order by any_value(recordDate) asc,any_value(a.voucherNo) asc,any_value(b.list)"));
                foreach ($all['array'] as $item) {
                    $result[] = $item['projectId'];
                }
                break;
        }
        return $result;
    }

    /**
     * 明细科目样式
     */
    public function subjectDetailStyle(): string
    {
        //辅助选项
        $assitItem = static::rGet(self::$stid . self::page . 'Assit');
        $subjectId = static::rGet(self::$stid . self::page . 'SubjectId');
        $subject = query('subject', "id = '$subjectId'");
        //like查询数组
        $like = array();
        //全等查询数组
        $equal = array(
            'a.isTemplate' => '否',
            'a.coid' => self::$coid
        );
        $subjectIds = [];
        $this->loopSubjectIds($subjectId, $subjectIds);
        $and = " and b.subjectId in ('" . implode("','", $subjectIds) . "')";
        $arr        = $this->post('createTime') ? explode(' ~ ', $this->post('createTime')) : [date('Y-m', strtotime(accountDetailsClass::getMinDate())), date('Y-m', strtotime(accountDetailsClass::getMaxDate()))];
        $arr[0] = trim($arr[0]);
        $arr[1] = trim(end($arr));
        $startMonth = trim($arr[0]);
        $and .= " and date_format(a.recordDate,'%Y-%m') >= '$arr[0]' and date_format(a.recordDate,'%Y-%m') <='$arr[1]'";
        //获取查询结果
        $where = search(array("like" => $like, "=" => $equal)) . $and . "";
        $page = $this->page(array("table" => "voucher as a join voucher_detail as b on a.id = b.orderId", 'field' => "b.*,a.voucherNo,a.recordDate,a.voucherWordId", "where" => $where . voucherClass::joinWhere(), "order" => "a.recordDate asc,a.voucherNo asc,b.list"));

        static::rSet(self::$stid . self::page . 'sql', $page['fullSql']);
        static::rSet(self::$stid . self::page . 'time', $this->post('createTime'));

        $tr = '';
        $url = root . "control/adIndex.html?cla=voucherEdit&fun=home";
        $accountDetail = new accountDetailsClass();

        //获取表头
        foreach ($page['array'] as $index => $array) {
            $array['money'] = self::centToYuan($array['money']);
            $voucherWord = query('voucher_word', "id = '$array[voucherWordId]'");
            if ($index == 0) {
                //期初
                $initResult = $this->financeInit($subject['id'], $startMonth);
                $jieTd = $daiTd = '';
                if ($assitItem == '下级科目') {
                    $columnsJie = $this->getSubjectColumns($subject['id'], '借');
                    $columnsDai = $this->getSubjectColumns($subject['id'], '贷');
                    foreach ($columnsJie as $val) {
                        $jieTd .= "<td></td>";
                    }
                    foreach ($columnsDai as $val) {
                        $daiTd .= "<td></td>";
                    }
                } elseif ($assitItem == '末级科目') {
                    $columnsJie = [];
                    $this->loopSubjectIds($subject['id'], $columnsJie, '借');
                    foreach ($columnsJie as $val) {
                        $jieTd .= "<td></td>";
                    }
                    $columnsDai = [];
                    $this->loopSubjectIds($subject['id'], $columnsDai, '贷');
                    foreach ($columnsDai as $val) {
                        $daiTd .= "<td></td>";
                    }
                }
                $tr .= <<<eof
                 <tr>
                     <td>{$array['recordDate']}</td>
                     <td></td>
                     <td>期初余额</td>
                     <td>{$initResult['jieMoney']}</td>
                     <td>{$initResult['daiMoney']}</td>
                     <td>{$initResult['direction']}</td>
                     <td>{$initResult['balance']}</td>
                     $jieTd
                     $daiTd
                 </tr>
eof;
            }
            $pinzheng = $voucherWord['id'] ? componentClass::urlTitle($voucherWord['word'] . '-' . $array['voucherNo'], $url . "&id={$array['orderId']}", '录凭证') : '';
            $array['money'] = number_format($array['money'], 2);

            $jieTd = '';
            $daiTd = '';
            if ($assitItem == '下级科目') {
                $columnsJie = $this->getSubjectColumns($subject['id'], '借');
                $columnsDai = $this->getSubjectColumns($subject['id'], '贷');
                //借
                foreach ($columnsJie as $val) {
                    $childOption = [];
                    $this->loopSubjectIds($val, $childOption);
                    if (in_array($array['subjectId'], $childOption)) {
                        $jieTd .= "<td>" . $array['money'] . "</td>";
                    } else {
                        $jieTd .= "<td></td>";
                    }
                }
                //贷
                foreach ($columnsDai as $val) {
                    $childOption = [];
                    $this->loopSubjectIds($val, $childOption);
                    if (in_array($array['subjectId'], $childOption)) {
                        $daiTd .= "<td>" . $array['money'] . "</td>";
                    } else {
                        $daiTd .= "<td></td>";
                    }
                }
            } elseif ($assitItem == '末级科目') {
                $columnsJie = [];
                $this->loopSubjectIds($subject['id'], $columnsJie, '借');
                foreach ($columnsJie as $val) {
                    if ($array['subjectId'] == $val) {
                        $jieTd .= "<td>" . $array['money'] . "</td>";
                    } else {
                        $jieTd .= "<td></td>";
                    }
                }
                $columnsDai = [];
                $this->loopSubjectIds($subject['id'], $columnsDai, '贷');
                foreach ($columnsDai as $val) {
                    if ($array['subjectId'] == $val) {
                        $daiTd .= "<td>" . $array['money'] . "</td>";
                    } else {
                        $daiTd .= "<td></td>";
                    }
                }
            }

            $jieMoney = $daiMoney = '';
            $balance = $accountDetail->balance($array['recordDate'], $array['subjectId'], $array['voucherNo']);


            if ($array['direction'] == '借') {
                $jieMoney = $array['money'];
            } else {
                $daiMoney = $array['money'];
            }
            $tr .= <<<eof
                 <tr>
                     <td>{$array['recordDate']}</td>
                     <td>$pinzheng</td>
                     <td>{$array['summary']}</td>
                     <td>$jieMoney</td>
                     <td>$daiMoney</td>
                     <td>{$balance['direction']}</td>
                     <td>{$balance['balance']}</td>
                     $jieTd
                     $daiTd
                 </tr>
eof;
            if (!isset($page['array'][$index + 1]['recordDate']) || date('Y-m', strtotime($page['array'][$index + 1]['recordDate'])) != date('Y-m', strtotime($array['recordDate']))) {
                //本期
                $jieTd = $daiTd = '';
                $periodMonth = date('Y-m', strtotime($array['recordDate']));
                $selfPeriod = $this->periodEnd($subject['id'], $periodMonth, 'selfPeriod');
                if ($assitItem == '下级科目') {
                    $columnsJie = $this->getSubjectColumns($subject['id'], '借');
                    $columnsDai = $this->getSubjectColumns($subject['id'], '贷');
                    foreach ($columnsJie as $val) {
                        $res = $this->selfPeriod($val, $periodMonth);
                        $jieTd .= "<td>" . number_format($res['jieMoney'], 2) . "</td>";
                    }

                    foreach ($columnsDai as $val) {
                        $res = $this->selfPeriod($val, $periodMonth);
                        $daiTd .= "<td>" . number_format($res['daiMoney'], 2) . "</td>";
                    }
                } elseif ($assitItem == '末级科目') {
                    $columnsJie = [];
                    $this->loopSubjectIds($subject['id'], $columnsJie, '借');
                    foreach ($columnsJie as $val) {
                        $res = $this->selfPeriod($val, $periodMonth);
                        $jieTd .= "<td>" . number_format($res['jieMoney'], 2) . "</td>";
                    }
                    $columnsDai = [];
                    $this->loopSubjectIds($subject['id'], $columnsDai, '贷');
                    foreach ($columnsDai as $val) {
                        $res = $this->selfPeriod($val, $periodMonth);
                        $daiTd .= "<td>" . number_format($res['daiMoney'], 2) . "</td>";
                    }
                }

                $jieYearTd = $daiYearTd = '';//本年累计
                $yearPeriod = $this->periodEnd($subject['id'], $periodMonth, 'yearPeriod');
                if ($assitItem == '下级科目') {
                    $columnsJie = $this->getSubjectColumns($subject['id'], '借');
                    $columnsDai = $this->getSubjectColumns($subject['id'], '贷');
                    foreach ($columnsJie as $val) {
                        $res = $this->yearPeriod($val, $periodMonth);
                        $jieYearTd .= "<td>" . number_format($res['jieMoney'], 2) . "</td>";
                    }

                    foreach ($columnsDai as $val) {
                        $res = $this->yearPeriod($val, $periodMonth);
                        $daiYearTd .= "<td>" . number_format($res['daiMoney'], 2) . "</td>";
                    }
                } elseif ($assitItem == '末级科目') {
                    $columnsJie = [];
                    $this->loopSubjectIds($subject['id'], $columnsJie, '借');
                    foreach ($columnsJie as $val) {
                        $res = $this->yearPeriod($val, $periodMonth);
                        $jieYearTd .= "<td>" . number_format($res['jieMoney'], 2) . "</td>";
                    }
                    $columnsDai = [];
                    $this->loopSubjectIds($subject['id'], $columnsDai, '贷');
                    foreach ($columnsDai as $val) {
                        $res = $this->yearPeriod($val, $periodMonth);
                        $daiYearTd .= "<td>" . number_format($res['daiMoney'], 2) . "</td>";
                    }
                }

                //期末余额
                $balancePeriod = $this->periodEnd($subject['id'], $periodMonth, 'balancePeriod');
                $jieBalanceTd = $daiBalanceTd = '';//期末余额
                if ($assitItem == '下级科目') {
                    $columnsJie = $this->getSubjectColumns($subject['id'], '借');
                    $columnsDai = $this->getSubjectColumns($subject['id'], '贷');
                    foreach ($columnsJie as $val) {
                        $res = $this->balancePeriod($val, $periodMonth);
                        $jieBalanceTd .= "<td>" . number_format($res['jieMoney'], 2) . "</td>";
                    }

                    foreach ($columnsDai as $val) {
                        $res = $this->balancePeriod($val, $periodMonth);
                        $daiBalanceTd .= "<td>" . number_format($res['daiMoney'], 2) . "</td>";
                    }
                } elseif ($assitItem == '末级科目') {
                    $columnsJie = [];
                    $this->loopSubjectIds($subject['id'], $columnsJie, '借');
                    foreach ($columnsJie as $val) {
                        $res = $this->balancePeriod($val, $periodMonth);
                        $jieBalanceTd .= "<td>" . number_format($res['jieMoney'], 2) . "</td>";
                    }
                    $columnsDai = [];
                    $this->loopSubjectIds($subject['id'], $columnsDai, '贷');
                    foreach ($columnsDai as $val) {
                        $res = $this->balancePeriod($val, $periodMonth);
                        $daiBalanceTd .= "<td>" . number_format($res['daiMoney'], 2) . "</td>";
                    }
                }
                $tr .= <<<eof
                 <tr  class="bold">
                     <td>$periodMonth</td>
                     <td></td>
                     <td>本期合计</td>
                     <td>{$selfPeriod['jieMoney']}</td>
                     <td>{$selfPeriod['daiMoney']}</td>
                     <td>{$selfPeriod['direction']}</td>
                     <td>{$selfPeriod['balance']}</td>
                     $jieTd
                     $daiTd
                 </tr>
                 <tr  class="bold">
                     <td>$periodMonth</td>
                     <td></td>
                     <td>本年累计</td>
                     <td>{$yearPeriod['jieMoney']}</td>
                     <td>{$yearPeriod['daiMoney']}</td>
                     <td>{$yearPeriod['direction']}</td>
                     <td>{$yearPeriod['balance']}</td>
                     $jieYearTd
                     $daiYearTd
                 </tr>
                 <tr  class="bold">
                     <td>$periodMonth</td>
                     <td></td>
                     <td>期末余额</td>
                     <td></td>
                     <td></td>
                     <td>{$balancePeriod['direction']}</td>
                     <td>{$balancePeriod['balance']}</td>
                     $jieBalanceTd
                     $daiBalanceTd
                 </tr>
eof;
            }

        }

        $jieTd = $daiTd = '';
        $jieHide = $daiHide = '';
        $jieColumnsNum = $daiColumnsNum = 1;
        if ($assitItem == '下级科目') {
            $columns = $this->getSubjectColumns($subject['id'], '借');
            foreach ($columns as $item) {
                $subjectInfo = query('subject', "id = '$item'");
                $jieTd .= "<td>{$subjectInfo['name']}</td>";
            }
            $jieColumnsNum = count($columns);
            $columns = $this->getSubjectColumns($subject['id'], '贷');
            foreach ($columns as $item) {
                $subjectInfo = query('subject', "id = '$item'");
                $daiTd .= "<td>{$subjectInfo['name']}</td>";
            }
            $daiColumnsNum = count($columns);
        } elseif ($assitItem == '末级科目') {
            $columns = [];
            $this->loopSubjectIds($subject['id'], $columns, '借');
            foreach ($columns as $item) {
                $subjectInfo = query('subject', "id = '$item'");
                $jieTd .= "<td>{$subjectInfo['name']}</td>";
            }
            $jieColumnsNum = count($columns);
            $columns = [];
            $this->loopSubjectIds($subject['id'], $columns, '贷');
            foreach ($columns as $item) {
                $subjectInfo = query('subject', "id = '$item'");
                $daiTd .= "<td>{$subjectInfo['name']}</td>";
            }
            $daiColumnsNum = count($columns);
        }
        if (empty($jieTd)) {
            $jieHide = 'hide';
        }
        if (empty($daiTd)) {
            $daiHide = 'hide';
        }
        // 月份选择器
        $monthSelector = componentClass::monthSelector(implode('~',$arr));
        $balanceBut = $jieBut = $datBut = '';
        if($jieHide && $daiHide){
            $balanceBut = $monthSelector['button'];
        }elseif($daiHide){
            $jieBut = $monthSelector['button'];
        }else{
            $datBut = $monthSelector['button'];
        }
        $html = <<<eof
<table id="subjectTable" class="tableMany">
    <thead>
        <tr>
            <td rowspan="2" style="vertical-align:middle">日期</td>
            <td rowspan="2" style="vertical-align:middle">凭证号</td>
            <td rowspan="2" style="vertical-align:middle">摘要</td>
            <td rowspan="2" style="vertical-align:middle">借方</td>
            <td rowspan="2" style="vertical-align:middle">贷方</td>
            <td rowspan="2" style="vertical-align:middle">方向</td>
            <td rowspan="2" style="vertical-align:middle">余额 {$balanceBut}</td>
            <td colspan="$jieColumnsNum" class="center $jieHide">借方 {$jieBut}</td>
            <td colspan="$daiColumnsNum" class="center $daiHide">贷方 {$datBut}</td>
        </tr>
        <tr>
            $jieTd
            $daiTd
        </tr>
    </thead>
    
    <tbody>
       $tr
    </tbody>
</table>
<style>
#subjectTable td{white-space:nowrap}
#subjectTable{width: unset;min-width: 1500px;margin-left: 0}
</style>
eof;
        //控件
        $button = "";
//        if ($this->power(self::page, "export")) {
//            $button .= "  <a href='" . root . "control/adIndex.html?cla=" . $this->get('cla') . "&fun=exportData'><span class='spanButton'>导出</span></a>\n";
//        }
        //返回
        return $page['hint'] .
            "<div class='acct-main-container'>
                <form name='listForm'>" .
                    $html .
                "</form>" .
                $monthSelector['html'].
            "</div>" .
            $page['button'] .
            clear;
    }

    /**
     * 获取列
     */
    public function getSubjectColumns($subjectId, $type): array
    {
        $result = [];
        $where = $type == '借' ? " and direction = '借'" : " and direction = '贷'";
        $all = find(array('table' => 'subject', 'where' => "pid = '$subjectId' $where order by list asc,level asc,selfIndex asc"));
        foreach ($all['array'] as $array) {

            $result[] = $array['id'];
        }
        return $result;
    }


    /**
     * 获取某个科目下的所有末级科目
     */
    public function loopSubjectIds($subjectId, &$result, $type = '')
    {
        $all = find(array('table' => 'subject', "where" => "pid = '$subjectId' and coid = '" . self::$coid . "' order by code asc"));
        foreach ($all['array'] as $array) {
            $this->loopSubjectIds($array['id'], $result, $type);
        }
        $subject = query('subject', "id = '$subjectId'");
        if (count($all['array']) == 0) {
            if (!$type || $subject['direction'] == $type) {
                $result[] = $subjectId;
            }
        }
    }

    /**
     * 获取某个科目及它所有的下级科目
     * @param $subjectId
     * @return array
     */
    public static function loopSubjectAllChildren($subjectId){
        $result[] = $subjectId;
        $all = find(array('table' => 'subject', "where" => "pid = '$subjectId' and coid = '" . self::$coid . "' order by code asc"));
        foreach ($all['array'] as $array) {
            $result = array_merge($result,self::loopSubjectAllChildren($array['id']));
        }
        return $result;
    }

    /**
     * 获取某个科目及它所有的上级科目
     */
    public static function loopSubjectPre($subjectId): array
    {
        $result[] = $subjectId;
        $subject = query('subject',"id = '$subjectId'");
        $all = find(array('table' => 'subject', "where" => "id = '$subject[pid]' and coid = '" . self::$coid . "' order by code desc"));
        foreach ($all['array'] as $array) {
            $result = array_merge($result,self::loopSubjectPre($array['id']));
        }
        return $result;
    }

    /**
     *
     * @param $subjectId
     * @param $startMonth
     * @param string $type
     * @return array
     */
    public function loopInit($subjectId, $startMonth, $type = 'init')
    {
        //查询此科目下的所有科目期初
        $result['jieMoney'] = 0;
        $result['daiMoney'] = 0;
        $subject = query('subject', "id = '$subjectId'");
        $all = find(array('table' => 'subject', "where" => "pid = '$subjectId' and coid = '" . self::$coid . "' order by list asc,level asc,selfIndex asc"));

        foreach ($all['array'] as $array) {
            $res = $this->loopInit($array['id'], $startMonth, $type);
            $result['jieMoney'] += $res['jieMoney'];
            $result['daiMoney'] += $res['daiMoney'];
        }

        if (count($all['array']) == 0) {
            $result = match ($type) {
                'selfPeriod' => $this->selfPeriod($subject['id'], $startMonth),//本期
                'yearPeriod' => $this->yearPeriod($subject['id'], $startMonth),//本年
                'balancePeriod' => $this->balancePeriod($subject['id'], $startMonth),//期末余额
                default => $this->initMoney($subject['id'], $startMonth)
            };
        }
        return $result;
    }

    /**
     * 期初余额
     * @param $subjectId
     * @param $startMonth
     * @return array
     */
    public function initMoney($subjectId, $startMonth): array
    {
        $subject = query('subject', "id = '$subjectId'");
        //期初金额
        $jieInit = find(array("table" => "voucher as a join voucher_detail as b on a.id = b.orderId", 'field' => "ifnull(sum(b.money),0) as money", "single" => "a.isTemplate = '否' and a.coid = '" . self::$coid . "' and b.subjectId = '$subject[id]' and b.direction = '借' and date_format(a.recordDate,'%Y-%m') < '$startMonth'" . voucherClass::joinWhere()));
        $daiInit = find(array("table" => "voucher as a join voucher_detail as b on a.id = b.orderId", 'field' => "ifnull(sum(b.money),0) as money", "single" => "a.isTemplate = '否' and a.coid = '" . self::$coid . "' and b.subjectId = '$subject[id]' and b.direction = '贷' and date_format(a.recordDate,'%Y-%m') < '$startMonth'" . voucherClass::joinWhere()));
        if ($subject['direction'] == '借') {
            $jieInit['money'] = $jieInit['money'] + $subject['initMoney'];
        } else {
            $daiInit['money'] = $daiInit['money'] + $subject['initMoney'];
        }
        $jieInit['money'] = self::centToYuan($jieInit['money']);
        $daiInit['money'] = self::centToYuan($daiInit['money']);
        $jieMoney = $jieInit['money'];
        $daiMoney = $daiInit['money'];
        $jieMoney += self::centToYuan($subject['borrowMoney']);//借方
        $daiMoney += self::centToYuan($subject['loanMoney']);//贷方
        return [
            'jieMoney' => round($jieMoney,2),
            'daiMoney' => round($daiMoney,2),
        ];
    }

    /**
     * 本期合计
     * @param $subjectId
     * @param $month
     * @return array
     */
    public function selfPeriod($subjectId, $month): array
    {
        $subject = query('subject', "id = '$subjectId'");
        $where = " and date_format(a.recordDate,'%Y-%m') = '$month'";
        $jieInit = find(array("table" => "voucher as a join voucher_detail as b on a.id = b.orderId", 'field' => "ifnull(sum(b.money),0) as money,ifnull(sum(b.voucherNum),0) as num", "single" => "a.isTemplate = '否' and a.coid = '" . self::$coid . "' and b.subjectId = '$subject[id]' and b.direction = '借'" . $where . voucherClass::joinWhere()));
        $daiInit = find(array("table" => "voucher as a join voucher_detail as b on a.id = b.orderId", 'field' => "ifnull(sum(b.money),0) as money,ifnull(sum(b.voucherNum),0) as num", "single" => "a.isTemplate = '否' and a.coid = '" . self::$coid . "' and b.subjectId = '$subject[id]' and b.direction = '贷'" . $where . voucherClass::joinWhere()));

        $jieInit['money'] = self::centToYuan($jieInit['money']);
        $daiInit['money'] = self::centToYuan($daiInit['money']);
        return [
            'jieMoney' => round($jieInit['money'],2),
            'daiMoney' => round($daiInit['money'],2)
        ];
    }

    /**
     * 本年累计
     * @param $subjectId
     * @param $month
     * @return array
     */
    public function yearPeriod($subjectId, $month)
    {
        $year = date('Y', strtotime($month . '-01'));
        $subject = query('subject', "id = '$subjectId'");
        $where = " and date_format(a.recordDate,'%Y-%m') <= '$month' and date_format(a.recordDate,'%Y') ='$year'";
        $jieInit = find(array("table" => "voucher as a join voucher_detail as b on a.id = b.orderId", 'field' => "ifnull(sum(b.money),0) as money,ifnull(sum(b.voucherNum),0) as num", "single" => "a.isTemplate = '否' and a.coid = '" . self::$coid . "' and b.subjectId = '$subject[id]' and b.direction = '借'" . $where . voucherClass::joinWhere()));
        $daiInit = find(array("table" => "voucher as a join voucher_detail as b on a.id = b.orderId", 'field' => "ifnull(sum(b.money),0) as money,ifnull(sum(b.voucherNum),0) as num", "single" => "a.isTemplate = '否' and a.coid = '" . self::$coid . "' and b.subjectId = '$subject[id]' and b.direction = '贷'" . $where . voucherClass::joinWhere()));
        $jieMoney = $subject['borrowMoney'];
        $daiMoney = $subject['loanMoney'];
        $jieInit['money'] = self::centToYuan($jieInit['money'] + $jieMoney);
        $daiInit['money'] = self::centToYuan($daiInit['money'] + $daiMoney);
        return [
            'jieMoney' => round($jieInit['money'],2),
            'daiMoney' => round($daiInit['money'],2)
        ];
    }


    public function balancePeriod($subjectId, $month)
    {
        $subject = query('subject', "id = '$subjectId'");
        $where = " and date_format(a.recordDate,'%Y-%m') <= '$month'";
        $jieInit = find(array("table" => "voucher as a join voucher_detail as b on a.id = b.orderId", 'field' => "ifnull(sum(b.money),0) as money,ifnull(sum(b.voucherNum),0) as num", "single" => "a.isTemplate = '否' and a.coid = '" . self::$coid . "' and b.subjectId = '$subject[id]' and b.direction = '借'" . $where . voucherClass::joinWhere()));
        $daiInit = find(array("table" => "voucher as a join voucher_detail as b on a.id = b.orderId", 'field' => "ifnull(sum(b.money),0) as money,ifnull(sum(b.voucherNum),0) as num", "single" => "a.isTemplate = '否' and a.coid = '" . self::$coid . "' and b.subjectId = '$subject[id]' and b.direction = '贷'" . $where . voucherClass::joinWhere()));
        if ($subject['direction'] == '借') {
            $jieInit['money'] = $jieInit['money'] + $subject['initMoney'];
        } else {
            $daiInit['money'] = $daiInit['money'] + $subject['initMoney'];
        }
        $jieInit['money'] = self::centToYuan($jieInit['money']);
        $daiInit['money'] = self::centToYuan($daiInit['money']);
        return [
            'jieMoney' => round($jieInit['money'],2),
            'daiMoney' => round($daiInit['money'],2)
        ];
    }

    /**
     * 期初
     * @param $subjectId
     * @param $startMonth
     * @return array
     */
    public function financeInit($subjectId, $startMonth)
    {
        $result = $this->loopInit($subjectId, $startMonth);
        if ($result['jieMoney'] == $result['daiMoney'] && $result['jieMoney'] == 0) {
            $direction = '平';
            $result['jieMoney'] = 0;
            $result['daiMoney'] = 0;
            $balance = 0;
        } elseif ($result['jieMoney'] > $result['daiMoney']) {
            $direction = '借';
            $balance = abs($result['jieMoney'] - $result['daiMoney']);
        } else {
            $direction = '贷';
            $balance = abs($result['jieMoney'] - $result['daiMoney']);
        }
        return accountDetailsClass::formatArrayNumber(['jieMoney' => $result['jieMoney'], 'daiMoney' => $result['daiMoney'], 'direction' => $direction, 'balance' => $balance]);
    }

    /**
     * 本期
     * @param $subjectId
     * @param $startMonth
     * @param $type
     * @return array
     */
    public function periodEnd($subjectId, $startMonth,$type): array
    {
        //本期
        $result = $this->loopInit($subjectId, $startMonth,$type);
        if ($result['jieMoney'] == $result['daiMoney'] && $result['jieMoney'] == 0) {
            $direction = '平';
            $result['jieMoney'] = 0;
            $result['daiMoney'] = 0;
            $balance = 0;
        } elseif ($result['jieMoney'] > $result['daiMoney']) {
            $direction = '借';
            $balance = abs($result['jieMoney'] - $result['daiMoney']);
        } else {
            $direction = '贷';
            $balance = abs($result['jieMoney'] - $result['daiMoney']);
        }
        return accountDetailsClass::formatArrayNumber(['jieMoney' => round($result['jieMoney'],2), 'daiMoney' => round($result['daiMoney'],2), 'direction' => $direction, 'balance' => round($balance,2)]);
    }

    /**
     * 导出
     */
    public function exportData()
    {
        if (!$this->power(self::page, 'export')) {
            $_SESSION['warn'] = '你还没有导出的权限';
        } else {
            $accountDetail = new accountDetailsClass();
            $subjectId = static::rGet(self::$stid . self::page . 'SubjectId');
            $subject = query('subject', "id = '$subjectId'");
            $time = static::rGet(self::$stid . self::page . 'time');
            $sql = static::rGet(self::$stid . self::page . 'sql');
            $where = static::rGet(self::$stid . self::page . 'where');
            //辅助选项
            $assitItem = static::rGet(self::$stid . self::page . 'Assit');
            $arr        = $time ? explode(' ~ ', $time) : [date('Y-m', strtotime(accountDetailsClass::getMinDate())), date('Y-m', strtotime(accountDetailsClass::getMaxDate()))];
            $arr[0] = trim($arr[0]);
            $arr[1] = trim(end($arr));
            $startMonth = trim($arr[0]);
            $title = array("日期", "凭证号", "摘要", '借方', '贷方', '方向', '余额');
            if ($subject['assistAccount'] == '是') {
                //借-列
                $columns = $this->getColumns($where, '借');
                $title = array_merge($title, $this->excelTitle($columns, $assitItem, '借'));
                //贷-列
                $columns = $this->getColumns($where, '贷');
                $title = array_merge($title, $this->excelTitle($columns, $assitItem, '贷'));
                $all = self::sqlFind($sql);
                $tr = [];
                //获取表头
                foreach ($all as $index => $array) {
                    $array['money'] = self::centToYuan($array['money']);
                    $voucherWord = query('voucher_word', "id = '$array[voucherWordId]'");
                    if ($index == 0) {
                        //期初
                        $initResult = $accountDetail->initMoney($subject['id'], $startMonth);
                        $jieTd = [];
                        $columns = $this->getColumns($where, '借');
                        foreach ($columns as $val) {
                            $jieTd[] = '';
                        }
                        $daiTd = [];
                        $columns = $this->getColumns($where, '贷');
                        foreach ($columns as $val) {
                            $daiTd[] = '';
                        }
                        $tr[] = array_merge([
                            $array['recordDate'],
                            '',
                            '期初余额',
                            $initResult['jieMoney'],
                            $initResult['daiMoney'],
                            $initResult['direction'],
                            $initResult['balance'],
                        ], $jieTd, $daiTd);

                    }

                    //借
                    $columns = $this->getColumns($where, '借');
                    $array['money'] = number_format($array['money'], 2);
                    $jieTd = $this->excelContent($columns, $assitItem, $array, '借');
                    //贷
                    $columns = $this->getColumns($where, '贷');
                    $daiTd = $this->excelContent($columns, $assitItem, $array, '贷');
                    $jieMoney = $daiMoney = '';
                    $balance = $accountDetail->balance($array['recordDate'], $subject['id'], $array['voucherNo']);
                    if ($array['direction'] == '借') {
                        $jieMoney = $array['money'];
                    } else {
                        $daiMoney = $array['money'];
                    }

                    $tr[] = array_merge([
                        $array['recordDate'],
                        $voucherWord['word'] . '-' . $array['voucherNo'],
                        $array['summary'],
                        $jieMoney,
                        $daiMoney,
                        $balance['direction'],
                        $balance['balance']
                    ], $jieTd, $daiTd);

                }


            } else {
                if ($assitItem == '下级科目') {
                    $columns = $this->getSubjectColumns($subject['id'], '借');
                    foreach ($columns as $item) {
                        $subjectInfo = query('subject', "id = '$item'");
                        $title[] = $subjectInfo['name'];
                    }
                    $columns = $this->getSubjectColumns($subject['id'], '贷');
                    foreach ($columns as $item) {
                        $subjectInfo = query('subject', "id = '$item'");
                        $title[] = $subjectInfo['name'];
                    }
                } elseif ($assitItem == '末级科目') {
                    $columns = [];
                    $this->loopSubjectIds($subject['id'], $columns, '借');
                    foreach ($columns as $item) {
                        $subjectInfo = query('subject', "id = '$item'");
                        $title[] = $subjectInfo['name'];
                    }
                    $columns = [];
                    $this->loopSubjectIds($subject['id'], $columns, '贷');
                    foreach ($columns as $item) {
                        $subjectInfo = query('subject', "id = '$item'");
                        $title[] = $subjectInfo['name'];
                    }
                }

                $tr = [];
                $all = self::sqlFind($sql);
                foreach ($all as $index => $array) {
                    $array['money'] = self::centToYuan($array['money']);
                    $voucherWord = query('voucher_word', "id = '$array[voucherWordId]'");
                    if ($index == 0) {
                        //期初
                        $initResult = $this->financeInit($subject['id'], $startMonth);
                        $jieTd = $daiTd = [];
                        if ($assitItem == '下级科目') {
                            $columnsJie = $this->getSubjectColumns($subject['id'], '借');
                            $columnsDai = $this->getSubjectColumns($subject['id'], '贷');
                            foreach ($columnsJie as $val) {
                                $jieTd[] = '';
                            }
                            foreach ($columnsDai as $val) {
                                $daiTd[] = '';
                            }
                        } elseif ($assitItem == '末级科目') {
                            $columnsJie = [];
                            $this->loopSubjectIds($subject['id'], $columnsJie, '借');
                            foreach ($columnsJie as $val) {
                                $jieTd[] = '';
                            }
                            $columnsDai = [];
                            $this->loopSubjectIds($subject['id'], $columnsDai, '贷');
                            foreach ($columnsDai as $val) {
                                $daiTd[] = '';
                            }
                        }
                        $tr[] = array_merge([
                            $array['recordDate'],
                            '',
                            '期初余额',
                            $initResult['jieMoney'],
                            $initResult['daiMoney'],
                            $initResult['direction'],
                            $initResult['balance'],
                        ], $jieTd, $daiTd);
                    }
                    $array['money'] = number_format($array['money'], 2);

                    $jieTd = $daiTd = [];
                    if ($assitItem == '下级科目') {
                        $columnsJie = $this->getSubjectColumns($subject['id'], '借');
                        $columnsDai = $this->getSubjectColumns($subject['id'], '贷');
                        //借
                        foreach ($columnsJie as $val) {
                            $childOption = [];
                            $this->loopSubjectIds($val, $childOption);
                            if (in_array($array['subjectId'], $childOption)) {
                                $jieTd[] = $array['money'];
                            } else {
                                $jieTd[] = "";
                            }
                        }
                        //贷
                        foreach ($columnsDai as $val) {
                            $childOption = [];
                            $this->loopSubjectIds($val, $childOption);
                            if (in_array($array['subjectId'], $childOption)) {
                                $daiTd[] = $array['money'];
                            } else {
                                $daiTd[] = "";
                            }
                        }
                    } elseif ($assitItem == '末级科目') {
                        $columnsJie = [];
                        $this->loopSubjectIds($subject['id'], $columnsJie, '借');
                        foreach ($columnsJie as $val) {
                            if ($array['subjectId'] == $val) {
                                $jieTd[] = $array['money'];
                            } else {
                                $jieTd[] = "";
                            }
                        }
                        $columnsDai = [];
                        $this->loopSubjectIds($subject['id'], $columnsDai, '贷');
                        foreach ($columnsDai as $val) {
                            if ($array['subjectId'] == $val) {
                                $daiTd[] = $array['money'];
                            } else {
                                $daiTd[] = "";
                            }
                        }
                    }

                    $jieMoney = $daiMoney = '';
                    $balance = $accountDetail->balance($array['recordDate'], $array['subjectId'], $array['voucherNo']);
                    if ($array['direction'] == '借') {
                        $jieMoney = $array['money'];
                    } else {
                        $daiMoney = $array['money'];
                    }
                    $tr[] = array_merge([
                        $array['recordDate'],
                        $voucherWord['word'] . '-' . $array['voucherNo'],
                        $array['summary'],
                        $jieMoney,
                        $daiMoney,
                        $balance['direction'],
                        $balance['balance']
                    ], $jieTd, $daiTd);
                }


            }


            $text = $this->control['name'] . "导出了" . self::title;
            $this->logText(["type" => self::page, "text" => $text]);
            $this->excelOut($title, $tr, self::title . ".xlsx");
        }
        header("Location:" . getenv("HTTP_REFERER"));
        exit(0);
    }

}
